﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FastData
{
    public class SqlServer : SqlClientBase, ISqlClient
    {
        private string connStr = "";
        private SqlConnection conn;
        private object lock_sql = new object();//上锁
        public string ConnStr
        {
            get
            {
                //"Database = 'master'; Data Source = '127.0.0.1'; User Id = 'sa'; Password = '123456789'; charset = 'utf8';"
                //"server=.;database=" + txt1.Text.Trim() + ";uid=test;pwd=test;connect timeout=5"
                connStr = $"Database = '{database}'; server = '{datasource}'; uid = '{userid}'; pwd = '{password}'; connect timeout=2;";
                return connStr;
            }
        }

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="ConnStr">连接数据库的SQL 语句</param>
        /// 语句类似于："Database='DB';Data Source='localhost';User Id='root';Password='12345678';charset='utf8';pooling=true"
        /// <returns></returns>
        public bool SqlConn()
        {
            lock (lock_sql)
            {
                try
                {
                    //"Database = 'master'; Data Source = '127.0.0.1'; User Id = 'sa'; Password = '123456789'; charset = 'utf8';"
                    //"server=.;database=" + txt1.Text.Trim() + ";uid=test;pwd=test;connect timeout=5";

                    conn = new SqlConnection(ConnStr);
                    if (!CheckStates())
                    {
                        conn.Open();
                        return CheckStates();//再检查一遍
                    }
                    else
                    {
                        return true;
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        public bool SqlConnTest()
        {
            try
            {
                //"Database = 'master'; Data Source = '127.0.0.1'; User Id = 'sa'; Password = '123456789'; charset = 'utf8';"
                //"server=.;database=" + txt1.Text.Trim() + ";uid=test;pwd=test;connect timeout=5";
                SqlConnection conn = new SqlConnection(ConnStr);
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }


        public bool IsConnected
        {
            get
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
        }

        //ok
        public void CaculateStartEnd(string tablename, int lastcount, out int _start, out int _end)
        {
            lock (lock_sql)
            {
                if (CheckStatesAndReconnection())//检查数据库是否打开
                {
                    string sqlcount = " select count(*) from " + tablename;
                    SqlCommand cmdcount = new SqlCommand(sqlcount, conn);
                    SqlDataAdapter adaptercount = new SqlDataAdapter();
                    adaptercount.SelectCommand = cmdcount;
                    DataSet dscount = new DataSet();
                    adaptercount.Fill(dscount);
                    adaptercount.Dispose();
                    //清除参数
                    cmdcount.Parameters.Clear();
                    cmdcount.Dispose();
                    string countstr = dscount.Tables[0].Rows[0][0].ToString();
                    int count = int.Parse(countstr);
                    int start = 0, end = 0;
                    if (count - lastcount >= 0)
                    {
                        start = count - lastcount;
                        end = lastcount;
                    }
                    else
                    {
                        start = 0;
                        end = count;
                    }
                    _start = start;
                    _end = end;
                }
                else
                {
                    throw new Exception("当前数据库没有连接，请检查");
                }
            }
        }

        //ok
        public string gen_SQL_tb_last(string tablename, int lastcount,string PrimaryKey = "Index")
        {

            int start, end;
            CaculateStartEnd(tablename, lastcount, out start, out end);
            string sql0 = $"select COLUMN_NAME  from INFORMATION_SCHEMA.COLUMNS where ORDINAL_POSITION = 1 AND TABLE_NAME = '{tablename}'";
            SqlCommand cmd0 = new SqlCommand(sql0, conn);
            SqlDataAdapter adapter0 = new SqlDataAdapter();
            adapter0.SelectCommand = cmd0;
            DataSet ds0 = new DataSet();
            adapter0.Fill(ds0);
            adapter0.Dispose();
            //清除参数
            cmd0.Parameters.Clear();
            cmd0.Dispose();
            var colname = ds0.Tables[0].Rows[0][0].ToString();
            string sql = $"select top {end} * from [{tablename}] where [{colname}] not IN(select top {start} [{colname}] from [{tablename}])";
            return sql;

        }
        //ok
        public DataSet FindLastData(string tablename, string PrimaryKey="Index")
        {
            lock (lock_sql)
            {
                if (CheckStatesAndReconnection())//检查数据库是否打开
                {
                    int start, end;
                    CaculateStartEnd(tablename, 1, out start, out end);
                    string str = gen_SQL_tb_last(tablename, 1);
                    String sql = str;
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    adapter.Dispose();
                    //清除参数
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    return ds;
                }
                else
                {
                    throw new Exception("当前数据库没有连接，请检查");
                }
            }
        }

        //ok
        public DataSet FindFirstData(string tablename)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            if (conn.State != ConnectionState.Open)
            {
                throw new Exception("数据库打开失败！");
            }
            string sql = $"select top 1 * from [{tablename}]";
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            adapter.Dispose();
            //清除参数
            cmd.Parameters.Clear();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return ds;


        }

        //OK
        public int DeleteData(string tableName, string[] columnName, object[] value)
        {

            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            if (conn.State != ConnectionState.Open)
            {
                throw new Exception("数据库打开失败！");
            }
            string sql = "DELETE FROM [" + tableName + "] WHERE ";
            for (int i = 0; i < columnName.Length; i++)
            {
                if (i == 0)
                    sql += $"[{columnName[0]}]='{value[0]}'";
                else
                {
                    sql += $"AND  [{columnName[i]}]='{value[i]}'";
                }
            }
            string sql_command = sql;
            SqlCommand cmd = new SqlCommand(sql_command, conn);
            int ret = cmd.ExecuteNonQuery();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return ret;

        }

        //ok
        public List<string> FindAllTables()
        {
            lock (lock_sql)
            {
                if (CheckStatesAndReconnection())//检查数据库是否打开
                {
                    string sql = "SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
                    SqlCommand cmd = new SqlCommand(sql, conn);//数据写入数据库
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    adapter.Dispose();
                    //清除参数
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    DataTable dt = ds.Tables[0];
                    List<string> list = new List<string>();
                    foreach (DataRow element in dt.Rows)
                    {
                        list.Add(element[0].ToString());
                    }
                    return list;
                }
                else
                {
                    throw new Exception("当前数据库没有连接，请检查");
                }
            }
        }

        //ok
        public DataSet FindLastLines(string tablename, int lastcount,string PrimaryKey="Index")
        {
            lock (lock_sql)
            {
                if (CheckStatesAndReconnection())//检查数据库是否打开
                {
                    string str = gen_SQL_tb_last(tablename, lastcount);
                    string sql = str;
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    adapter.Dispose();
                    //清除参数
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    return ds;
                }
                else
                {
                    throw new Exception("当前数据库没有连接，请检查");
                }
            }
        }

        public DataSet FindAllLines(string tablename)
        {
            lock (lock_sql)
            {
                if (CheckStatesAndReconnection())//检查数据库是否打开
                {
                    string str = " select * from [" + tablename + "]";
                    SqlCommand cmdall = new SqlCommand(str, conn);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmdall;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    adapter.Dispose();
                    return ds;
                }
                else
                {
                    throw new Exception("当前数据库没有连接，请检查");
                }
            }
        }

        //ok
        public List<string> FindAllTableColums(string tablename)
        {
            lock (lock_sql)
            {
                if (CheckStatesAndReconnection())//检查数据库是否打开
                {
                    string sql = "SELECT name FROM syscolumns where id = object_id('" + tablename + "')";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    adapter.Dispose();
                    //清除参数
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    DataTable dt = ds.Tables[0];
                    List<string> list = new List<string>();
                    foreach (DataRow element in dt.Rows)
                    {
                        list.Add(element[0].ToString());
                    }
                    return list;
                }
                else
                {
                    throw new Exception("当前数据库没有连接，请检查");
                }
            }
        }

        //ok
        public DataSet FindAllLike(string tablename, int lastcount, string column, object like)
        {
            lock (lock_sql)
            {
                if (CheckStatesAndReconnection())//检查数据库是否打开
                {
                    string sql = "SELECT * FROM (" + gen_SQL_tb_last(tablename, lastcount) + ") as tb WHERE  [" + column + "] like  '%" + like + "%'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    adapter.Dispose();
                    //清除参数
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    return ds;
                }
                else
                {
                    throw new Exception("当前数据库没有连接，请检查");
                }
                //DataTable dt = ds.Tables[0];
                //List<string> list = new List<string>();
                //foreach (DataRow element in dt.Rows)
                //{
                //    list.Add(element[0].ToString());
                //}
                //return list;
            }
        }



        /// <summary>
        /// 关闭于数据库的连接
        /// </summary>
        public bool SqlClose()
        {
            try
            {
                if (CheckStates())//检查数据库当前的连接状态
                {
                    conn.Close();
                    return !CheckStates();//这里取反了，所以，最后return的是true，就说明关闭成功了；反之。
                }
                else
                {
                    return true;
                }
            }
            catch (Exception)
            {
               return false;
            }

        }



        /// <summary>
        /// 检查数据库的连接状态
        /// </summary>
        public bool CheckStates()
        {
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    //连接成功
                    return true;
                }
                else
                {
                    //连接失败
                    return false;
                }
            }
            catch (Exception)
            {
                //连接失败
                return false;
            }

        }

        /// <summary>
        /// 检查数据库的连接状态，如果没有连接，重连；反之
        /// </summary>
        /// <param name="connStr">连接数据库的sql语句</param>
        /// <returns></returns>
        public bool CheckStatesAndReconnection()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    //连接成功
                    return true;
                }
                else
                {
                    if (SqlConn())
                    {
                        //连接成功
                        return true;
                    }
                    else
                    {
                        //连接失败
                        return false;
                    }
                }
            }
            else
            {
                if (SqlConn())
                {
                    //连接成功
                    return true;
                }
                else
                {
                    //连接失败
                    return false;
                }
            }
        }







        //*********************************向数据库写入数据***********************************//
        //***********************************************************************************//
        //***********************************************************************************//


        /// <summary>
        /// 通过SQL语句向数据库中插入数据。
        /// 用这个的话，就需要自己写完整的sql语句，没有其他的两个方法用起来方便
        /// </summary>
        /// <param name="sql_command">要插入数据的sql命令语句</param>
        /// <param name="sql_connection">连接数据库的sql命令</param>
        /// <returns></returns>
        /// 
        public bool InsertData(string sql_command)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            if (conn.State != ConnectionState.Open)
            {
                throw new Exception("数据库打开失败！");
            }
            SqlCommand cmd = new SqlCommand(sql_command, conn);
            int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return result == 1 ? true : false;

        }


        public bool Insert(string tableName, string[] columnNames, object[] values, ref long Index)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            if (conn.State != ConnectionState.Open)
            {
                throw new Exception("数据库打开失败！");
            }

            string cNames = "(";
            for (int i = 0; i < columnNames.Length; i++)//这个for循环是为了将所有列名整合成一个字符串语句的。如下例
            {
                if (i == columnNames.Length - 1)
                {
                    cNames += "[" + columnNames[i] + "])";
                }
                else
                {
                    cNames += "[" + columnNames[i] + "],";
                }
            }

            //cNames = "(`生产时间`,`1号电子秤称重前重量 / g`,`1号秤判稳时间 / ms`,`1号秤称重结果`,`2号电子秤称重前重量 / g`,`2号秤判稳时间 / ms`,`2号秤称重结果`,`注液泵转速 / RPM`,`液体密度 / g/ml`,`注液量R / g`,`注液量L / g`)";
            string sql = "INSERT INTO [" + tableName + "] " + cNames + "VALUES (";
            for (int i = 0; i < values.Count(); i++)//这个for循环是为了补全sql语句的
            {
                if (i == values.Count() - 1)
                {
                    sql = sql + "'" + values[i] + "')";
                }
                else
                {
                    sql = sql + String.Format("'{0}',", values[i]);
                }
            }
            SqlCommand cmd = new SqlCommand(sql, conn);//数据写入数据库
            int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return result == 1 ? true : false;//判断写入是否成功

        }

        /// <summary>
        /// 通过SQL语句向数据库指定列插入数据
        /// 这些值是string类型的List数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnNames">对应列名称</param>
        /// <param name="values">对应要插入的值</param>    
        /// <param name="sql_connection">连接数据库的sql命令</param>   
        /// <returns></returns>
        public bool InsertGluingSystemData(string tableName, string[] columnNames, List<string> values)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库是否打开
                    {
                        string cNames = string.Empty;
                        for (int i = 0; i < columnNames.Length; i++)//这个for循环是为了将所有列名整合成一个字符串语句的。如下例
                        {
                            if (i == 0)
                            {
                                cNames += "([" + columnNames[i] + "],";
                            }
                            else if (i > 0 && i < columnNames.Length - 1)
                            {
                                cNames += "[" + columnNames[i] + "],";
                            }
                            else
                            {
                                cNames += "[" + columnNames[i] + "])";
                            }
                        }

                        //cNames = "(`生产时间`,`1号电子秤称重前重量 / g`,`1号秤判稳时间 / ms`,`1号秤称重结果`,`2号电子秤称重前重量 / g`,`2号秤判稳时间 / ms`,`2号秤称重结果`,`注液泵转速 / RPM`,`液体密度 / g/ml`,`注液量R / g`,`注液量L / g`)";
                        string sql = "INSERT INTO [" + tableName + "] " + cNames;
                        for (int i = 0; i < values.Count; i++)//这个for循环是为了补全sql语句的
                        {
                            if (i == 0)
                            {
                                sql = sql + "VALUES ('" + values[i].ToString() + "',";
                            }
                            else if (i == values.Count - 1)
                            {
                                sql = sql + String.Format("'{0}')", values[i].ToString());
                            }
                            else
                            {
                                sql = sql + String.Format("'{0}',", values[i].ToString());
                            }
                        }
                        SqlCommand cmd = new SqlCommand(sql, conn);//数据写入数据库
                        int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
                        cmd.Dispose();
                        return result == 1 ? true : false;//判断写入是否成功
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }



        /// <summary>
        /// 通过SQL语句向数据库指定列插入数据
        /// 这些值可以是任意数据类型的数据集合，都存储在以values为名的list集合中
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnNames">对应列名称</param>
        /// <param name="values">对应要插入的值</param>
        /// <param name="sql_connection">连接数据库的sql命令</param>
        /// <returns></returns>
        public bool InsertGluingSystemData(string tableName, string[] columnNames, List<object> values)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库是否打开
                    {
                        string cNames = string.Empty;
                        for (int i = 0; i < columnNames.Length; i++)//这个for循环是为了将所有列名整合成一个字符串语句的。如下例
                        {
                            if (i == 0)
                            {
                                cNames += "([" + columnNames[i] + "],";
                            }
                            else if (i > 0 && i < columnNames.Length - 1)
                            {
                                cNames += "[" + columnNames[i] + "],";
                            }
                            else
                            {
                                cNames += "[" + columnNames[i] + "])";
                            }
                        }

                        //cNames = "(`生产时间`,`1号电子秤称重前重量 / g`,`1号秤判稳时间 / ms`,`1号秤称重结果`,`2号电子秤称重前重量 / g`,`2号秤判稳时间 / ms`,`2号秤称重结果`,`注液泵转速 / RPM`,`液体密度 / g/ml`,`注液量R / g`,`注液量L / g`)";
                        string sql = "INSERT INTO [" + tableName + "] " + cNames;
                        for (int i = 0; i < values.Count; i++)//这个for循环是为了补全sql语句的
                        {
                            if (i == 0)
                            {
                                sql = sql + "VALUES ('" + values[i].ToString() + "',";
                            }
                            else if (i == values.Count - 1)
                            {
                                sql = sql + String.Format("'{0}')", values[i].ToString());
                            }
                            else
                            {
                                sql = sql + String.Format("'{0}',", values[i].ToString());
                            }
                        }
                        SqlCommand cmd = new SqlCommand(sql, conn);//数据写入数据库
                        int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
                        cmd.Dispose();
                        return result == 1 ? true : false;//判断写入是否成功
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }





        //*********************************检索数据库中数据***********************************//
        //***********************************************************************************//
        //***********************************************************************************//


        /// <summary>
        /// 根据sql语句，检索数据库中指定数据，定反馈到Dataset数据集中
        /// </summary>
        /// <param name="sql_command">用于检索的sql命令</param>
        /// <param name="sql_connection">连接数据库的sql命令</param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql_command)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            if (conn.State != ConnectionState.Open)
            {
                throw new Exception("数据库连接失败！");
            }
            //创建一个SqlCommand对象
            SqlCommand cmd = new SqlCommand(sql_command, conn);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            //清除参数
            cmd.Parameters.Clear();
            conn.Close();
            conn.Dispose();
            return ds;
        }


        /// <summary>
        /// 检索某列内一定范围的数据，并反馈到Dataset数据集中。
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="start">起始位置</param>
        /// <param name="end">结束为止</param>
        /// <param name="sql_connection">数据库连接的sql语句</param>
        /// <returns></returns>
        public DataSet GetDataSet(string tableName, string columnName, string start, string end)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string sql_command = string.Format("SELECT * FROM  [{0}] WHERE [{1}] BETWEEN '{2}'AND '{3}'", tableName, columnName, start, end);//update的sql语句

                        //创建一个SqlCommand对象
                        SqlCommand cmd = new SqlCommand(sql_command, conn);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        //清除参数
                        cmd.Parameters.Clear();
                        return ds;
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }

        public DataSet GetData(string tableName, string columnName, string value)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string sql_command = string.Format("SELECT * FROM  [{0}] WHERE [{1}]  = '{2}'", tableName, columnName, value);//update的sql语句

                        //创建一个SqlCommand对象
                        SqlCommand cmd = new SqlCommand(sql_command, conn);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        //清除参数
                        cmd.Parameters.Clear();
                        return ds;
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }

        public DataSet GetNewData(string tableName, string[] columnName, object[] value)
        {
            throw new Exception("该接口未实现");
        }


        public DataSet GetFirstData(string tableName, string[] columnName, object[] value)
        {
            throw new Exception("该接口未实现");
        }

        public DataSet GetData(string tableName, string[] columnName, object[] value)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string sql = "SELECT * FROM [" + tableName + "] WHERE ";

                        for (int i = 0; i < columnName.Length; i++)
                        {
                            if (i == 0)
                                sql += $"[{columnName[0]}]='{value[0]}'";
                            else
                            {
                                sql += $"AND  [{columnName[i]}]='{value[i]}'";
                            }
                        }
                        string sql_command = sql;
                        //创建一个SqlCommand对象
                        SqlCommand cmd = new SqlCommand(sql_command, conn);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        //清除参数
                        cmd.Parameters.Clear();
                        return ds;
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }

        /// <summary>
        /// 检索某列内某一项的数据，并反馈到Dataset数据集中。
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="value">值</param>
        /// <param name="sql_connection">数据库连接的sql语句</param>
        /// <returns></returns>
        public DataSet GetDataSet(string tableName, string columnName, string value)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string sql_command = string.Format("SELECT * FROM  [{0}] WHERE [{1}]  LIKE'{2}%'", tableName, columnName, value);//update的sql语句

                        //创建一个SqlCommand对象
                        SqlCommand cmd = new SqlCommand(sql_command, conn);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        //清除参数
                        cmd.Parameters.Clear();
                        return ds;
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }


        /// <summary>
        /// 根据分页来获取数据，返回DataSet数据
        /// </summary>
        /// <param name="strSql">Select字符串语句</param>
        /// <param name="pageSize">每页的行数</param>
        /// <returns></returns>
        public DataSet GetDataByPagination(string selectSql, int pageSize)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        DataSet srcDataSet = new DataSet();
                        try
                        {
                            using (SqlDataAdapter command = new SqlDataAdapter(selectSql, conn))
                            {
                                command.Fill(srcDataSet, "ds");
                                int number = srcDataSet.Tables[0].Rows.Count / pageSize;
                                if ((srcDataSet.Tables[0].Rows.Count - number * pageSize) > 0)
                                    number++;
                                DataTable[] dts = new DataTable[number];
                                int i = 0;
                                for (i = 0; i < number; i++)
                                {
                                    dts[i] = srcDataSet.Tables[0].Clone();
                                    dts[i].TableName = string.Format("dt{0}", i);
                                    DataRow[] dataRows = srcDataSet.Tables[0].Rows.OfType<DataRow>().Skip(i * pageSize).Take(pageSize).ToArray();
                                    dts[i] = dataRows.CopyToDataTable();
                                }
                                //i = 0;
                                //foreach (DataRow r in srcDataSet.Tables[0].Rows)
                                //{
                                //    dts[i / pageSize].Rows.Add(r.ItemArray);
                                //    i++;
                                //}
                                foreach (DataTable dt in dts)
                                    srcDataSet.Tables.Add(dt);
                            }
                            return srcDataSet;
                        }
                        catch (SqlException e)
                        {
                            throw new Exception(e.Message);
                        }
                        catch (Exception e)
                        {
                            throw e;
                        }
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }

        }


        ///按照时间的检索方式，他同样可以扩展为检索某一列 某个范围内的 数据
        /// 
        //string startTime = dtpStart.Value.ToString("yyyy-MM-dd HH:mm:ss");//起始时间
        //string endTime = dtpEnd.Value.ToString("yyyy-MM-dd HH:mm:ss");//结束时间
        //
        //string sql = string.Format("SELECT * FROM 表名 WHERE 列名 BETWEEN '{0}'AND '{1}'", startTime, endTime);//sql语句，检索两个时间段内的所有数据

        ///模糊搜索的方式，搜索某一项的所有数据
        //string sql = string.Format("SELECT * FROM 表名 WHERE  列名 LIKE'{0}%'", "检索项名称");







        //*********************************修改数据库中数据***********************************//
        //***********************************************************************************//
        //***********************************************************************************//


        /// <summary>
        /// 修改数据库中的数据
        /// 通用
        /// </summary>
        /// <param name="sql_command">用于检索的sql命令</param>
        /// <param name="sql_connection">连接数据库的sql命令</param>
        /// <returns></returns>
        public bool Update(string sql_command)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            if (conn.State != ConnectionState.Open)
            {
                throw new Exception("数据库打开失败！");
            }
            SqlCommand cmd = new SqlCommand(sql_command, conn);//数据写入数据库
            int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
            cmd.Dispose();
            conn.Close();
            return result >= 1 ? true : false;//判断写入是否成功
        }





        /// <summary>
        /// 修改数据库中的数据（整表操作）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="column1Name">要修改值的列名</param>
        /// <param name="value1">要修改的值</param>
        /// <param name="sql_connection">连接数据库的sql语句</param>
        /// <returns></returns>
        public bool Update(string tableName, string column1Name, string value1)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string sql_command = string.Format("update [{0}] set [{1}] = '{2}'", tableName, column1Name, value1);//update的sql语句
                        SqlCommand cmd = new SqlCommand(sql_command, conn);//数据写入数据库
                        int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
                        cmd.Dispose();
                        return result >= 1 ? true : false;//判断写入是否成功
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }






        /// <summary>
        /// 修改数据库中的数据（表内指定范围的数据操作）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="column1Name">要修改值的列名</param>
        /// <param name="value1">要修改的值</param>
        /// <param name="column2Name">用于限定范围的列名</param>
        /// <param name="value2">用于限定范围的列名的值</param>
        /// <param name="sql_connection">连接数据库的sql语句</param>
        /// <returns></returns>

        public bool Update(string tableName, string column2Name, string value2, string column1Name, object value1)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string sql_command = string.Format("update [{0}] set [{1}] = '{2}' where [{3}] ='{4}' ", tableName, column1Name, value1, column2Name, value2);//update的sql语句
                        SqlCommand cmd = new SqlCommand(sql_command, conn);//数据写入数据库
                        int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
                        cmd.Dispose();
                        return result >= 1 ? true : false;//判断写入是否成功
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }


        /// <summary>
        /// 修改数据库中的多列的数据（表内指定范围的数据操作）--多列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="column1Name">要修改列名数组</param>
        /// <param name="value1">要修改的值</param>
        /// <param name="column2Name">用于限定范围的列名</param>
        /// <param name="value2">用于限定范围的列名的值</param>
        /// <param name="sql_connection">连接数据库的sql语句</param>
        /// <returns></returns>
        public bool Update(string tableName, string[] column2Name, object[] value2, string[] column1Name, object[] value1)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string x = "";
                        for (int i = 0; i < column1Name.Length; i++)
                        {
                            if (i == 0)
                            {
                                x += "["+ column1Name[i] + "]='" + value1[i] + "'";
                            }
                            else
                            {
                                x += ",[" + column1Name[i] + "]='" + value1[i] + "'";
                            }
                        }

                        string sql_command = string.Format("update [{0}] set {1} where [{2}] ='{3}' ", tableName, x, column2Name[0], value2[0]);
                        for (int i = 1; i < column2Name.Length; i++)
                        {
                            sql_command += $"AND [{column2Name[i]}]={value2[i]}";
                        }

                        SqlCommand cmd = new SqlCommand(sql_command, conn);//数据写入数据库
                        int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
                        cmd.Dispose();
                        return result >= 1 ? true : false;//判断写入是否成功
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }


        /// <summary>
        /// 修改数据库中的多列的数据（表内指定范围的数据操作）--多列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="column1Name">要修改列名数组</param>
        /// <param name="value1">要修改的值</param>
        /// <param name="column2Name">用于限定范围的列名1</param>
        /// <param name="value2">用于限定范围的列名的值1</param>
        /// <param name="column3Name">用于限定范围的列名2</param>
        /// <param name="value3">用于限定范围的列名的值2</param>
        /// <param name="sql_connection">连接数据库的sql语句</param>
        /// <returns></returns>
        public bool Update(string tableName, string[] column1Name, string[] value1, string column2Name, string value2, string column3Name, string value3)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string x = "";
                        for (int i = 0; i < column1Name.Length; i++)
                        {
                            if (i == 0)
                            {
                                x += "[" + column1Name[i] + "]='" + value1[i] + "'";
                            }
                            else
                            {
                                x += ",[" + column1Name[i] + "]='" + value1[i] + "'";
                            }
                        }
                        string sql_command = string.Format("update [{0}] set {1} where [{2}] ='{3}' &&[{4}]='{5}' ", tableName, x, column2Name, value2, column3Name, value3);//update的sql语句                                              
                        SqlCommand cmd = new SqlCommand(sql_command, conn);//数据写入数据库
                        int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
                        cmd.Dispose();
                        return result >= 1 ? true : false;//判断写入是否成功
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }


        //update `tbforntwight_data` SET `前称` = '321',`NO1` = '1.23' where `条码` ='200000037971' 

        /// updata 的sql语句
        /// 
        ///  string sql = string.Format("update 表名 set 列名1 = 'value1' where 列名2 = 'value2' ");
        /// 说明：
        /// 检索“表”中，列名2='value2'的那些项，将它们的 列名1 的那些列的值，全部替换成'value1'。







        //*********************************删除数据库中数据***********************************//
        //***********************************************************************************//
        //***********************************************************************************//


        /// <summary>
        /// 删除数据中的数据
        /// 通用
        /// </summary>
        /// <param name="sql_command">删除的sql命令</param>
        /// <param name="sql_connection">连接数据库的sql语句</param>
        /// <returns></returns>
        public bool Delete(string sql_command)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        SqlCommand cmd = new SqlCommand(sql_command, conn);//数据写入数据库
                        int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
                        cmd.Dispose();
                        return result >= 1 ? true : false;//判断数据删除是否成功
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }



        /// <summary>
        /// 删除数据中的数据（指定列的同类数据）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="value">值</param>
        /// <param name="sql_connection">连接数据库的sql语句</param>
        /// <returns></returns>
        public bool Delete(string tableName, string columnName, string value)
        {
            lock (lock_sql)
            {
                try
                {
                    if (CheckStatesAndReconnection())//检查数据库的连接状态
                    {
                        string sql_command = string.Format("delete from [{0}] where [{1}] ='{2}' ", tableName, columnName, value);//delete的sql语句

                        SqlCommand cmd = new SqlCommand(sql_command, conn);//数据写入数据库
                        int result = cmd.ExecuteNonQuery();//检查数据库内受影响的行数
                        cmd.Dispose();
                        return result >= 1 ? true : false;//判断数据删除是否成功
                    }
                    else
                    {
                        throw new Exception("当前数据库没有连接，请检查");
                    }
                }
                catch (Exception)
                {
                    //System.Windows.Forms.MessageBox.Show("MySqlHelper \r\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    throw;
                }
            }
        }
    }
}
